import copy

from flask import Flask, render_template, jsonify, request
import sqlite3

app = Flask(__name__)


def get_music_data(search=None, page=1, per_page=22):
    conn = sqlite3.connect('music_data.db')
    cursor = conn.cursor()
    
    # 构建 SQL 查询
    query = 'SELECT * FROM music'
    query2 = 'SELECT COUNT(*) FROM music'
    params = []
    
    if search:
        query += ' WHERE artist LIKE ? OR title LIKE ?'
        query2 += ' WHERE artist LIKE ? OR title LIKE ?'
        params.extend([f'%{search}%', f'%{search}%'])
    params2 = copy.deepcopy(params)
    
    # 计算分页的 OFFSET 和 LIMIT
    offset = (page - 1) * per_page
    query += ' LIMIT ? OFFSET ?'
    params.extend([per_page, offset])
    
    cursor.execute(query, params)
    
    rows = cursor.fetchall()
    data = []
    
    for row in rows:
        id, timestamp, artist, title = row
        data.append({
            'id': id,
            'timestamp': timestamp,  # 返回时间戳
            'artist': artist,
            'title': title
        })
    
    # 获取总记录数，用于计算总页数
    cursor.execute(query2, params2)
    total_records = cursor.fetchone()[0]
    
    conn.close()
    return data, total_records


@app.route('/')
def index():
    music_data = get_music_data()
    return render_template('index.html', music_data=music_data)


@app.route('/api/music', methods=['GET'])
def music_api():
    search = request.args.get('search')  # 获取查询参数
    page = int(request.args.get('page', 1))  # 获取当前页，默认为第1页
    per_page = int(request.args.get('per_page', 22))  # 获取每页数据大小，默认为22条
    
    music_data, total_records = get_music_data(search, page, per_page)
    
    return jsonify({
        'data': music_data,
        'total_records': total_records,
        'page': page,
        'per_page': per_page
    })


if __name__ == '__main__':
    app.run(debug=True)
